/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package dao;

import beans.ReporteCombustible;
import beans.ReporteEstadoPedido;
import beans.ReporteEstadoPedidoxDia;
import beans.ReporteHoraExtra;
import beans.ReporteHoraExtraxDia;
import beans.ReporteIncidencia;
import beans.ReporteZona;
import beans.SobreCosto;
import config.Parameters;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import request.ReporteCombustibleRequest;
import request.ReporteEstadoPedidoRequest;
import request.ReporteHoraExtraRequest;
import request.ReporteIncidenciaRequest;
import request.ReporteZonasRequest;
import request.SobreCostoRequest;
import response.ReporteCombustibleResponse;
import response.ReporteEstadoPedidoResponse;
import response.ReporteHoraExtraResponse;
import response.ReporteIncidenciaResponse;
import response.ReporteZonaResponse;

/**
 *
 * @author ZULLY
 */
public class ReporteDAO extends ConnectBD{

    public ReporteZonaResponse reporteZonas(ReporteZonasRequest reporte) throws SQLException {
        ReporteZonaResponse rpt = new ReporteZonaResponse();
        ArrayList<ReporteZona> listaReporteZona = new ArrayList<ReporteZona>();
        rpt.me = "";
        
        String sql;
        
        String [] fInicio = reporte.fechaInicio.split("-");
        String [] fFin = reporte.fechaFin.split("-");
        
        sql = "SELECT P.ciudad, SUM(P.cantidadProductoTotal) FROM pedido P " +
              "WHERE P.fechaPedido BETWEEN '"+ fInicio[2] + "-" + fInicio[1]+ "-"+fInicio[0]+ "' AND '"+ fFin[2] + "-" + fFin[1]+ "-"+fFin[0]+ 
              "' AND P.estado = 2 GROUP BY P.ciudad";
        
        Statement st = conexion.conn.createStatement();
        st.execute(sql);
        
        ResultSet rs = st.getResultSet();
        
        while(rs.next()){
            ReporteZona aux = new ReporteZona();
            aux.ciudad = rs.getString(1);
            aux.cantidad = rs.getInt(2);
            listaReporteZona.add(aux);
        }
        
        rpt.reporte = listaReporteZona;
        
        return rpt;
        
    }

    public ReporteHoraExtraResponse reporteHorasExtras(ReporteHoraExtraRequest reporte) throws SQLException {
        ReporteHoraExtraResponse rpt = new ReporteHoraExtraResponse();
        ArrayList<ReporteHoraExtra> listaReporteHoraExtra = new ArrayList<ReporteHoraExtra>();
        ArrayList<ReporteHoraExtraxDia> listaReporteHoraExtraxDia = new ArrayList<ReporteHoraExtraxDia>();
        rpt.me = "";
        
        String sql;
        
        String [] fInicio = reporte.fechaInicio.split("-");
        String [] fFin = reporte.fechaFin.split("-");
        
        sql = "SELECT H.idHoraExtra, CONCAT(U.nombre,' ',U.apellidoPaterno,' ',U.apellidoMaterno),H.fecha,H.horasExtras,H.turno,U.tipoUsuario "+
              "FROM hora_extra H " +
              "INNER JOIN usuario U ON H.idUsuario=U.idUSUARIO "+
              "WHERE H.fecha BETWEEN '"+ fInicio[2] + "-" + fInicio[1]+ "-"+fInicio[0]+ "' AND '"+ fFin[2] + "-" + fFin[1]+ "-"+fFin[0]+ "' "+
              "ORDER BY H.fecha";
        
        Statement st = conexion.conn.createStatement();
        st.execute(sql);
        
        ResultSet rs = st.getResultSet();
        
        while(rs.next()){
            ReporteHoraExtra aux = new ReporteHoraExtra();
            aux.idHoraExtra = rs.getInt(1);
            aux.nombre = rs.getString(2);
            aux.fecha = rs.getString(3).substring(0,10);
            aux.horasExtras = rs.getInt(4);
            if (rs.getInt(5)== 1){
                aux.turno = "Mañana";
            }
            else{
                if (rs.getInt(5)==2){
                    aux.turno = "Tarde";
                }
                else{
                    aux.turno = "Noche";
                }
            }
            if (rs.getInt(6)==2){
                aux.costoHorasExtras=aux.horasExtras*Parameters.COSTO_HORA_CARRO;
            }
            else{
                aux.costoHorasExtras=aux.horasExtras*Parameters.COSTO_HORA_MOTO;
            }
            listaReporteHoraExtra.add(aux);
        }
        
        rpt.reporte = listaReporteHoraExtra;
        
         sql = "SELECT H.fecha, SUM(IF(U.tipoUsuario = 2,H.horasExtras*"+Parameters.COSTO_HORA_CARRO +",H.horasExtras*"+Parameters.COSTO_HORA_MOTO+")) "+
              "FROM hora_extra H " +
              "INNER JOIN usuario U ON H.idUsuario=U.idUSUARIO "+
              "WHERE H.fecha BETWEEN '"+ fInicio[2] + "-" + fInicio[1]+ "-"+fInicio[0]+ "' AND '"+ fFin[2] + "-" + fFin[1]+ "-"+fFin[0]+ "' "+
              "GROUP BY H.fecha ORDER BY H.fecha";
        
        st = conexion.conn.createStatement();
        st.execute(sql);
        
        rs = st.getResultSet();
        
        while (rs.next()){
            ReporteHoraExtraxDia aux = new ReporteHoraExtraxDia();
            aux.fecha = rs.getString(1).substring(0,10);
            aux.costo = rs.getInt(2);
            listaReporteHoraExtraxDia.add(aux);
        }
        
        rpt.horaExtraxDia = listaReporteHoraExtraxDia;
        
        return rpt;        
    }

    public ReporteEstadoPedidoResponse reporteEstadoPedido(ReporteEstadoPedidoRequest reporte) throws SQLException {
        ReporteEstadoPedidoResponse rpt = new ReporteEstadoPedidoResponse();
        ArrayList<ReporteEstadoPedido> listaReporteEstadoPedido = new ArrayList<ReporteEstadoPedido>();
        ArrayList<ReporteEstadoPedidoxDia> listaReporteEstadoPedidoXDia = new ArrayList<ReporteEstadoPedidoxDia>();
        rpt.me ="";
        
        String sql;
        
        String [] fInicio = reporte.fechaInicio.split("-");
        String [] fFin = reporte.fechaFin.split("-");
        
        sql = "SELECT P.idPedido, CONCAT(U.nombre,' ',U.apellidoPaterno,' ',U.apellidoMaterno),P.cantidadProductoTotal,P.ciudad,PU.x,PU.y,P.fechaPedido,P.horaPedido,P.tiempoEntrega,P.retraso,P.estado "+
              "FROM pedido P " +
              "INNER JOIN usuario U ON P.idCLIENTE=U.idUSUARIO "+
              "INNER JOIN punto PU ON P.idPUNTO=PU.idPUNTO "+
              "WHERE P.fechaPedido BETWEEN '"+ fInicio[2] + "-" + fInicio[1]+ "-"+fInicio[0]+ "' AND '"+ fFin[2] + "-" + fFin[1]+ "-"+fFin[0]+ "' ";
              
        if (reporte.estado != -1){
            sql += "AND P.estado=" + reporte.estado+" ";
        }
        
        sql += "ORDER BY P.fechaPedido,P.horaPedido";
        
        Statement st = conexion.conn.createStatement();
        st.execute(sql);
        
        ResultSet rs = st.getResultSet();
        
        while(rs.next()){
            ReporteEstadoPedido aux = new ReporteEstadoPedido();
            aux.idPedido = rs.getInt(1);
            aux.nombreCliente = rs.getString(2);
            aux.cantidadProductoTotal = rs.getInt(3);
            aux.ciudad = rs.getString(4);
            aux.x=rs.getInt(5);
            aux.y=rs.getInt(6);
            aux.fechaPedido=rs.getString(7);
            aux.horaPedido = rs.getString(8);
            aux.tiempoEntrega=rs.getInt(9);
            aux.retraso=rs.getInt(10);
            if (aux.retraso < 0){
                double costoRetraso = Math.ceil(-1.0*aux.retraso/60);
                aux.costoRetraso= ((int)(costoRetraso))*Parameters.COSTO_FUERA_PLAZO;
            }
            else{
                aux.costoRetraso = 0;
            }           
            
            aux.estado=rs.getInt(11);
            listaReporteEstadoPedido.add(aux);
        }        
        rpt.reporte = listaReporteEstadoPedido;
        
        sql = "SELECT P.fechaPedido,SUM(IF(P.retraso<0,P.cantidadProductoTotal,0)),SUM(IF(P.retraso<0,P.retraso,0)),SUM(IF(P.estado = 1,P.cantidadProductoTotal,0)),SUM(IF(P.estado = 2,P.cantidadProductoTotal,0)) "+
              "FROM pedido P " +
              "WHERE P.fechaPedido BETWEEN '"+ fInicio[2] + "-" + fInicio[1]+ "-"+fInicio[0]+ "' AND '"+ fFin[2] + "-" + fFin[1]+ "-"+fFin[0]+ "' "+
              "GROUP BY P.fechaPedido ORDER BY P.fechaPedido";
        
        st = conexion.conn.createStatement();
        st.execute(sql);
        
        rs = st.getResultSet();
        
        while (rs.next()){
            ReporteEstadoPedidoxDia aux = new ReporteEstadoPedidoxDia();
            aux.fecha = rs.getString(1);
            aux.cantidadRetraso = rs.getInt(2);
            double costoRetraso = Math.ceil(-1.0*rs.getInt(3)/60);
            aux.costo = ((int)(costoRetraso))*Parameters.COSTO_FUERA_PLAZO;
            aux.cantidadProceso = rs.getInt(4);
            aux.cantidadEntregado = rs.getInt(5);
            listaReporteEstadoPedidoXDia.add(aux);
        }
        
        rpt.costoxDia = listaReporteEstadoPedidoXDia;
        return rpt;
    }

    public SobreCosto reporteSobreCosto(SobreCostoRequest reporte) throws SQLException {
        SobreCosto sobreCosto = new SobreCosto();
        
        sobreCosto.fechaInicio = reporte.fechaInicio;
        sobreCosto.fechaFin = reporte.fechaFin;
        sobreCosto.numeroAutos = Parameters.getNumCarros();
        sobreCosto.numeroMotos = Parameters.getNumMotos();
        
        
        sobreCosto.me ="";
        
        String sql;
        
        String [] fInicio = reporte.fechaInicio.split("-");
        String [] fFin = reporte.fechaFin.split("-");
        
        sql = "SELECT SUM(IF(P.retraso<0,P.cantidadProductoTotal,0)),SUM(IF(P.retraso<0,P.retraso,0)) "+
              "FROM pedido P " +
              "WHERE P.fechaPedido BETWEEN '"+ fInicio[2] + "-" + fInicio[1]+ "-"+fInicio[0]+ "' AND '"+ fFin[2] + "-" + fFin[1]+ "-"+fFin[0]+ "' ";
        
        Statement st = conexion.conn.createStatement();
        st.execute(sql);
        
        ResultSet rs = st.getResultSet();
                
        while (rs.next()){
            sobreCosto.numeroEntregasRetraso = rs.getInt(1);
            double costoRetraso = Math.ceil(-1.0*rs.getInt(2)/60);
            sobreCosto.costoEntregaRetraso= ((int)(costoRetraso))*Parameters.COSTO_FUERA_PLAZO;
        }
        
        sql = "SELECT SUM(H.horasExtras)"+
              "FROM hora_extra H "+
              "INNER JOIN usuario U ON H.idUsuario=U.idUSUARIO "+
              "WHERE H.fecha BETWEEN '"+ fInicio[2] + "-" + fInicio[1]+ "-"+fInicio[0]+ "' AND '"+ fFin[2] + "-" + fFin[1]+ "-"+fFin[0]+ "' " +
              "AND U.tipoUsuario = 4";
        
        st = conexion.conn.createStatement();
        st.execute(sql);
        
        rs = st.getResultSet();
        
        while (rs.next()){
            sobreCosto.numeroHoraExtraMotos = rs.getInt(1);
            sobreCosto.costoHoraExtraMotos = sobreCosto.numeroHoraExtraMotos*Parameters.COSTO_HORA_MOTO;
        }
        
        sql = "SELECT SUM(H.horasExtras)"+
              "FROM hora_extra H "+
              "INNER JOIN usuario U ON H.idUsuario=U.idUSUARIO "+
              "WHERE H.fecha BETWEEN '"+ fInicio[2] + "-" + fInicio[1]+ "-"+fInicio[0]+ "' AND '"+ fFin[2] + "-" + fFin[1]+ "-"+fFin[0]+ "' " +
              "AND U.tipoUsuario = 2";
        
        st = conexion.conn.createStatement();
        st.execute(sql);
        
        rs = st.getResultSet();
        
        while (rs.next()){
            sobreCosto.numeroHoraExtraAutos = rs.getInt(1);
            sobreCosto.costoHoraExtraAutos = sobreCosto.numeroHoraExtraAutos*Parameters.COSTO_HORA_CARRO;
        }
        
        sobreCosto.numeroKiloExtraMotos = sobreCosto.numeroHoraExtraMotos*Parameters.getVelocidadMoto();
        sobreCosto.costoKiloExtraMotos = sobreCosto.numeroKiloExtraMotos*Parameters.COSTO_MOTO;
        
        sobreCosto.numeroKiloExtraAutos = sobreCosto.numeroHoraExtraAutos*Parameters.getVelocidadMoto();
        sobreCosto.costoKiloExtraAutos = sobreCosto.numeroKiloExtraAutos*Parameters.COSTO_CARRO;       
        
        sobreCosto.totalSobreCosto = sobreCosto.costoEntregaRetraso+sobreCosto.costoHoraExtraAutos+sobreCosto.costoHoraExtraMotos+sobreCosto.costoKiloExtraAutos+sobreCosto.costoKiloExtraMotos;
        
        return sobreCosto;
    }

    public ReporteCombustibleResponse reporteCombustible(ReporteCombustibleRequest reporte) throws SQLException {
        ReporteCombustibleResponse rpt = new ReporteCombustibleResponse();
        ArrayList<ReporteCombustible> listaReporteCombustible = new ArrayList<ReporteCombustible>();
        rpt.me = "";
        
        String sql;
        
        String [] fInicio = reporte.fechaInicio.split("-");
        String [] fFin = reporte.fechaFin.split("-");
        
        sql = "SELECT H.fecha, SUM(IF(U.tipoUsuario = 2,H.horasExtras*"+Parameters.getVelocidadCarro() +",H.horasExtras*"+Parameters.getVelocidadMoto()+")), SUM(IF(U.tipoUsuario = 2,H.horasExtras*"+Parameters.getVelocidadCarro()*Parameters.COSTO_CARRO +",H.horasExtras*"+Parameters.getVelocidadMoto()*Parameters.COSTO_MOTO+")) "+
              "FROM hora_extra H " +
              "INNER JOIN usuario U ON H.idUsuario=U.idUSUARIO "+
              "WHERE H.fecha BETWEEN '"+ fInicio[2] + "-" + fInicio[1]+ "-"+fInicio[0]+ "' AND '"+ fFin[2] + "-" + fFin[1]+ "-"+fFin[0]+ "' "+              
              "GROUP BY H.fecha "+
              "ORDER BY H.fecha";
        
        Statement st = conexion.conn.createStatement();
        st.execute(sql);
        
        ResultSet rs = st.getResultSet();
        
        while(rs.next()){
            ReporteCombustible aux = new ReporteCombustible();
            aux.fecha = rs.getString(1).substring(0, 10);
            aux.cantidad = rs.getInt(2);
            aux.costo = rs.getInt(3);
            listaReporteCombustible.add(aux);
        }
        rpt.reporte = listaReporteCombustible;
        
        return rpt;
    }

    public ReporteIncidenciaResponse reporteIncidencia(ReporteIncidenciaRequest reporte) throws SQLException {
        
        ReporteIncidenciaResponse rpt = new ReporteIncidenciaResponse();
        ArrayList<ReporteIncidencia> listaReporteIncidencia = new ArrayList<ReporteIncidencia>();
        rpt.me = "";
        
        String sql;
        
        String [] fInicio = reporte.fechaInicio.split("-");
        String [] fFin = reporte.fechaFin.split("-");
        
        sql = "SELECT I.fechaOcurrencia, SUM(IF(I.turno = 1,I.tiempoPerdido,0)), SUM(IF(I.turno = 2,I.tiempoPerdido,0)), SUM(IF(I.turno = 3,I.tiempoPerdido,0)) "+
              "FROM incidencia I " +
              "WHERE I.fechaOcurrencia BETWEEN '"+ fInicio[2] + "-" + fInicio[1]+ "-"+fInicio[0]+ "' AND '"+ fFin[2] + "-" + fFin[1]+ "-"+fFin[0]+ "' "+              
              "GROUP BY I.fechaOcurrencia "+
              "ORDER BY I.fechaOcurrencia";
        
        Statement st = conexion.conn.createStatement();
        st.execute(sql);
        
        ResultSet rs = st.getResultSet();
        
        while(rs.next()){
            ReporteIncidencia aux = new ReporteIncidencia();
            aux.fecha = rs.getString(1).substring(0,10);
            aux.maniana = rs.getInt(2);
            aux.tarde = rs.getInt(3);
            aux.noche = rs.getInt(4);
            listaReporteIncidencia.add(aux);
        }
        rpt.reporte = listaReporteIncidencia;
        
        return rpt;
    }
}
